/**
 * 
 */
package com.feib.stms.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.JdbcUtils;

/**
 * @author Jimmy Liu
 *
 */
public class BasePaymentFileContentXlsResultSetExtractor implements ResultSetExtractor<Object> {
	
	protected Logger logger = LoggerFactory.getLogger(this.getClass());
	
	protected Sheet sheet;
	
	protected int rownum;
	
	protected Map<String, CellStyle> styles;

	public BasePaymentFileContentXlsResultSetExtractor(Sheet sheet, int rownum, Map<String, CellStyle> styles) {
		super();
		this.sheet = sheet;
		this.rownum = rownum;
		this.styles = styles;
	}

	/* (non-Javadoc)
	 * @see org.springframework.jdbc.core.ResultSetExtractor#extractData(java.sql.ResultSet)
	 */
	public Object extractData(ResultSet rs) throws SQLException,
			DataAccessException 
	{
		while (rs.next()) {
			
            // data rows

            Row row = sheet.createRow(rownum++);
            row.setHeightInPoints((float) 24);
            int j = 0;
            
            // 帳號
            String accountNoValue = (String)getColumnValue(rs, "ACCOUNT_NO", String.class);
            Cell accountNoCell = row.createCell(j++);
            if (null != accountNoValue)
            	accountNoCell.setCellValue(accountNoValue);
            accountNoCell.setCellStyle(styles.get("cell"));  

            // 帳號檢查碼M
            String accountNoChkValue = (String)getColumnValue(rs, "ACCOUNT_NO_CHK", String.class);
            Cell accountNoChkCell = row.createCell(j++);
            if (null != accountNoChkValue)
            	accountNoChkCell.setCellValue(accountNoChkValue);
            accountNoChkCell.setCellStyle(styles.get("cell"));

            // 客戶戶名
            String customerNameValue = (String)getColumnValue(rs, "CUSTOMER_NAME", String.class);
            Cell customerNameCell = row.createCell(j++);
            if (null != customerNameValue)
            	customerNameCell.setCellValue(customerNameValue);
            customerNameCell.setCellStyle(styles.get("cell")); 
            
            // 淨收金額
            Double netIncomeValue = (Double)getColumnValue(rs, "NET_INCOME", Double.class);
            Cell netIncomeCell = row.createCell(j++);
            if (null != netIncomeValue)
            	netIncomeCell.setCellValue(netIncomeValue);
            netIncomeCell.setCellStyle(styles.get("cell_number"));
            
            // 淨付金額
            Double netPayValue = (Double)getColumnValue(rs, "NET_PAY", Double.class);
            Cell netPayCell = row.createCell(j++);
            if (null != netPayValue)
            	netPayCell.setCellValue(netPayValue);
            netPayCell.setCellStyle(styles.get("cell_number"));

            // 金額檢查碼N
            String amountChkValue = (String)getColumnValue(rs, "AMOUNT_CHK", String.class);
            Cell amountChkCell = row.createCell(j++);
            if (null != amountChkValue)
            	amountChkCell.setCellValue(amountChkValue);
            amountChkCell.setCellStyle(styles.get("cell")); 

            // 處理類別
            String dealTypeValue = (String)getColumnValue(rs, "DEAL_TYPE", String.class);
            Cell dealTypeCell = row.createCell(j++);
            if (null != dealTypeValue)
            	dealTypeCell.setCellValue(dealTypeValue);
            dealTypeCell.setCellStyle(styles.get("cell")); 

            // 證券代號
            String stockNoValue = (String)getColumnValue(rs, "STOCK_NO", String.class);
            Cell stockNoCell = row.createCell(j++);
            if (null != stockNoValue)
            	stockNoCell.setCellValue(stockNoValue);
            stockNoCell.setCellStyle(styles.get("cell"));

            // 證券名稱
            String stockNameValue = (String)getColumnValue(rs, "STOCK_NAME", String.class);
            Cell stockNameCell = row.createCell(j++);
            if (null != stockNameValue)
            	stockNameCell.setCellValue(stockNameValue);
            stockNameCell.setCellStyle(styles.get("cell"));

            // 扣款優先種類
            String chargebackPriorityValue = (String)getColumnValue(rs, "CHARGEBACK_PRIORITY", String.class);
            Cell chargebackPriorityCell = row.createCell(j++);
            if (null != chargebackPriorityValue)
            	chargebackPriorityCell.setCellValue(chargebackPriorityValue);
            chargebackPriorityCell.setCellStyle(styles.get("cell")); 

            // 扣款先後序號
            String chargebackPriorityCodeValue = (String)getColumnValue(rs, "CHARGEBACK_PRIORITY_CODE", String.class);
            Cell chargebackPriorityCodeCell = row.createCell(j++);
            if (null != chargebackPriorityCodeValue)
            	chargebackPriorityCodeCell.setCellValue(chargebackPriorityCodeValue);
            chargebackPriorityCodeCell.setCellStyle(styles.get("cell"));

            // 交割日期
            Date deliveryDateValue = (Date)getColumnValue(rs, "DELIVERY_DATE", Date.class);
            Cell deliveryDateCell = row.createCell(j++);
            if (null != deliveryDateValue)
            	deliveryDateCell.setCellValue(deliveryDateValue);
            deliveryDateCell.setCellStyle(styles.get("cell_date"));
            

            // 營業員
            String tellerValue = (String)getColumnValue(rs, "TELLER", String.class);
            Cell tellerCell = row.createCell(j++);
            if (null != tellerValue)
            	tellerCell.setCellValue(tellerValue);
            tellerCell.setCellStyle(styles.get("cell"));

            // 處理狀況代號
            //String dealResultCodeValue = (String)getColumnValue(rs, "DEAL_RESULT_CODE", String.class);
            String dealResultCodeValue = (String)getColumnValue(rs, "DEAL_RESULT_CODE_NAME", String.class);
            Cell dealResultCodeCell = row.createCell(j++);
            if (null != dealResultCodeValue)
            	dealResultCodeCell.setCellValue(dealResultCodeValue);
            dealResultCodeCell.setCellStyle(styles.get("cell")); 

            /*
            // 身份證號
            String customerIdValue = (String)getColumnValue(rs, "CUSTOMER_ID", String.class);
            Cell customerIdCell = row.createCell(j++);
            if (null != customerIdValue)
            	customerIdCell.setCellValue(customerIdValue);
            customerIdCell.setCellStyle(styles.get("cell")); 
            */
		}
		
		return null;
	}
	
	/**
     * @return the rownum
     */
    public int getRownum() {
        return rownum;
    }
	
	protected Object getColumnValue(ResultSet rs, String columnName, Class<?> requiredType)
	{
		int index = 0;
		try {
			index = rs.findColumn(columnName);
		} catch (SQLException e) {
			logger.trace("資料集(ResultSet)中資料欄名稱 ["+columnName+"] 無效，請先確認SQL Statement 有Select 此欄位.", e);
		}
		

		try {
			if (index > 0)
				return JdbcUtils.getResultSetValue(rs, index, requiredType);
		} catch (SQLException e) {
			logger.trace("資料集(ResultSet)中資料欄名稱 ["+columnName+"] 欄位值錯誤，可能型別轉型錯誤(需要轉為 " + requiredType + " 型態)", e);
		}
		
		return null;
	}

}
